package de.sordul.jobMaster.impl;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.inject.Inject;

import de.sordul.jobMaster.IWordlistDAO;

/**
 * JDBC based WordlistDAO implementation.
 * @see de.sordul.jobMaster.IWordlistDAO
 */
public class WordlistDAO implements IWordlistDAO {
	
	@Inject
	private Logger log;

	private Connection con;
	private PreparedStatement st;
	private ResultSet rs;
	private String statement;
	
	public WordlistDAO() {
		try {
			Class.forName("org.h2.Driver");
		} catch (ClassNotFoundException e) {
			log.log(Level.WARNING, "Unable to get Database driver", e);
		}
	}
	
	@Override
	public void createByFile(File file) {
		try {
			// Setup connection
			con = DriverManager.getConnection("jdbc:h2:mem:jobMaster;DB_CLOSE_DELAY=-1", "sa", "");
			statement = "CREATE TABLE "+getTableNameFromFile(file.getAbsolutePath())+" AS SELECT * FROM CSVREAD('"+file.getAbsolutePath()+"','FRAGMENT1;FRAGMENT2;FRAGMENT3', 'charset=UTF-8 fieldSeparator=;')";
			
			// Create the entry
			st = con.prepareStatement(statement);
			st.executeUpdate();
			
		} catch (SQLException e) {
			log.log(Level.WARNING, "SQL error", e);
			
		} finally {
			
			// Close connection
			try { if(st!=null) st.close(); } catch( Exception e) { }
			try { if(con!=null) con.close(); } catch( Exception e) { }
		}
	}

	@Override
	public String readRandom(String language, String wordlist) {
		String result = "";
		
		try {
			// Setup connection
			con = DriverManager.getConnection("jdbc:h2:mem:jobMaster;DB_CLOSE_DELAY=-1", "sa", "");
			statement = "SELECT * FROM "+language+"_"+wordlist+" ORDER BY RAND()";
			
			// Get the result
			st = con.prepareStatement(statement);	
			rs = st.executeQuery();
			
			// Build result string omitting all empty fields
			int counter = 1;
			while( rs.next() ) {
				rs.getString(counter);
				if(!rs.wasNull()) {
					result += rs.getString(counter);
					if(counter < 3) {
						result += " ";
					} else {
						break;
					}
					counter++;
				}
			}
			
		} catch (SQLException e) {
			log.log(Level.WARNING, "SQL error", e);
			
		} finally {
			
			// Close connection
			try { if(st!=null) st.close(); } catch( Exception e) { }
			try { if(con!=null) con.close(); } catch( Exception e) { }
		}
		
		return result;
	}

	/**
	 * Returns a table name extracted from file path.
	 * @param filePath The File path
	 * @return Table name
	 */
	private String getTableNameFromFile(String filePath) {
		String result =	filePath.substring(filePath.lastIndexOf(File.separator)+1, filePath.lastIndexOf("."));
		return result;
	}
}
